﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

/*
 * Author:Conis
 * Date:
 * Description:对数据库的访问
 */

namespace Conis.Squirrel.SqlBuilder.SqlServer
{
    /// <summary>
    /// 对数据库的访问
    /// </summary>
    public class DataAccess : BaseDataAccess
    {

        public DataAccess(BaseSqlFactory sqlFactory, string tableName, string primaryKey) : 
            base(sqlFactory, tableName, primaryKey) { }
        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        protected override IDataParameter GetParameter(string key, object value)
        {
            return new SqlParameter(key, value);
        }

        /// <summary>
        /// 获取单页数据
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="condition"></param>
        /// <param name="fields"></param>
        /// <param name="orderBy"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected override DataTable GetSinglePage(Pagination pagination, string condition, string fields, string orderBy, params IDataParameter[] parameters)
        {
            string sql;
            //第一页，使用Top即可
            if (pagination.PageIndex == 1)
            {
                sql = string.Format("SELECT TOP {0} {1} FROM {2}{3}{4}",
                    pagination.PageSize, fields, this.TableName, condition, orderBy);
            }
            else
            {
                int startIndex = PaginationHelper.StartIndex(pagination.PageIndex, pagination.PageSize);
                int endIndex = PaginationHelper.EndIndex(startIndex, pagination.PageSize);
                sql = "SELECT {0} FROM ( SELECT ROW_NUMBER() OVER(ORDER BY {1}) AS RowNum,{0} FROM {2}{3}) T WHERE T.RowNum BETWEEN {4} and {5}{6}";
                sql = string.Format(sql, fields, this.PrimaryKey, this.TableName, condition, startIndex, endIndex, orderBy);
            }
            return this.SqlFactory.ExecuteSql.GetDataTable(sql, CommandType.Text, parameters);
        }

        /// <summary>
        /// 插入一条数据
        /// </summary>
        /// <param name="nameValue"></param>
        /// <param name="ignoreNull"></param>
        /// <param name="ignorePrimaryKey"></param>
        /// <returns></returns>
        public override object Insert(Hashtable nameValue, bool ignoreNull, bool ignorePrimaryKey)
        {
            IDataParameter[] parameters;
            string sql = this.GetInsertSql(nameValue, ignoreNull, ignorePrimaryKey, out parameters);
            sql += "; SELECT @@IDENTITY;";
            object result = this.SqlFactory.ExecuteSql.ExecuteScalar(sql, CommandType.Text, parameters);
            if (ignorePrimaryKey)
            {
                return result;
            }
            else
            {
                return nameValue[this.PrimaryKey];
            }
        }
    }
}
